package cn.com.dashihui.web.service;

import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;

import cn.com.dashihui.kit.ExcelExportUtil;
import cn.com.dashihui.kit.ExcelExportUtil.Pair;
import cn.com.dashihui.web.dao.Seller;
import cn.com.dashihui.web.dao.User;
import cn.com.dashihui.web.dao.UserChange;
import cn.com.dashihui.web.dao.UserValueLog;

public class ChangeDeliveryService {

	/**
	 * 查询所有公司
	 * @return
	 */
	public List<Seller> findAllSeller() {
		return Seller.me().find("select * from t_bus_seller");
	}
	/**
	 * 选择分公司
	 * @param store
	 * @return
	 */
	public List<Record> chooseStore(String seller) {
		return Db.find("select id,storeid,title from t_dict_store where sellerId in ('"+seller+"')");
	}
	/**
	 * 选择营业部
	 * @param store
	 * @return
	 */
	public List<Record> chooseDao(String store) {
		return Db.find("select S.id,S.trueName from t_auth_admin_seller as S "
				+ "left join t_auth_admin_roles_seller as R on S.id=R.userid where R.roleid =4 and storeid in ('"+store+"')");
	}
	public Page<Record> findByPage(int pageNum, int pageSize,String phone, String card, int toBossThreeId, int toBossTwoId, int toBossOneId,int match){
		StringBuffer sBuffer = new StringBuffer("FROM t_bus_user A "
				+ "left join t_bus_user_change D on A.phone = D.linkPhone "
				+ "left join t_dict_store E on A.toBossTwoId = E.id "
				+ "left join t_bus_user_bankid C on A.bankNoId = C.bankId "
				+ "left join t_auth_admin_seller B on A.toBossOneId = B.id "
				+ " where 1=1 ");
		List<Object> params = new ArrayList<Object>();
		if(StrKit.notBlank(phone)){
			sBuffer.append(" AND A.phone like ?");
			params.add("%"+phone+"%");
		}
		if(StrKit.notBlank(card)){
			sBuffer.append(" and A.card=?");
			params.add(card);
		}
		if(toBossThreeId!=0){
			sBuffer.append(" and A.toBossThreeId in ('"+toBossThreeId+"')");
		}
		if(toBossTwoId!=0){
			sBuffer.append(" and A.toBossTwoId in ('"+toBossTwoId+"')");
		}
		if(toBossOneId!=0){
			sBuffer.append(" and A.toBossOneId in ('"+toBossOneId+"')");
		}
		switch (match) {
		case 1:
			sBuffer.append(" and D.match=1 ");
			break;
		case 2:
			sBuffer.append(" and D.clientCode is null ");
			break;
		}
		sBuffer.append(" ORDER BY A.createDate DESC,A.id desc");
		return Db.paginate(pageNum, pageSize, "SELECT A.*,B.trueName ,C.bankName,D.clientCode,E.title", sBuffer.toString(),params.toArray());
	}
	/**
	 * 排序
	 * @param pageNum
	 * @param pageSize
	 * @param index
	 * @param phone
	 * @param card
	 * @param title
	 * @return
	 */
	public Page<Record> findByPages1(int pageNum, int pageSize,String index,String phone, String card, int toBossThreeId, int toBossTwoId, int toBossOneId) {
		StringBuffer sBuffer = new StringBuffer("FROM t_bus_user A "
				+ "left join t_bus_user_change D on A.phone = D.linkPhone "
				+ "left join t_dict_store E on A.toBossTwoId = E.id "
				+ "left join t_bus_user_bankid C on A.bankNoId = C.bankId "
				+ "left join t_auth_admin_seller B on A.toBossOneId = B.id "
				+ " where 1=1 ");
		List<Object> params = new ArrayList<Object>();
		if(StrKit.notBlank(phone)){
			sBuffer.append(" AND A.phone like ?");
			params.add("%"+phone+"%");
		}
		if(StrKit.notBlank(card)){
			sBuffer.append(" and A.card=?");
			params.add(card);
		}
		if(toBossThreeId!=0){
			sBuffer.append(" and A.toBossThreeId in ('"+toBossThreeId+"')");
		}
		if(toBossTwoId!=0){
			sBuffer.append(" and A.toBossTwoId in ('"+toBossTwoId+"')");
		}
		if(toBossOneId!=0){
			sBuffer.append(" and A.toBossOneId in ('"+toBossOneId+"')");
		}
		if(!StrKit.isBlank(index)){
			switch (index) {
			case "1"://积分
				sBuffer.append(" ORDER BY A.sppoint DESC");
				break;
			case "2":
				sBuffer.append(" ORDER BY A.sppoint ASC");
				break;
			case "3"://抵扣券
				sBuffer.append(" ORDER BY A.jkpoint DESC");
				break;
			case "4":
				sBuffer.append(" ORDER BY A.jkpoint ASC");
				break;	
			case "5"://优惠券
				sBuffer.append(" ORDER BY A.jypoint DESC");
				break;
			case "6":
				sBuffer.append(" ORDER BY A.jypoint ASC");
				break;
			case "7"://电子券
				sBuffer.append(" ORDER BY A.ecoupon DESC");
				break;
			case "8":
				sBuffer.append(" ORDER BY A.ecoupon ASC");
				break;	
		     }	
		}
		return Db.paginate(pageNum, pageSize, "SELECT A.* ,B.trueName ,C.bankName,D.clientCode,E.title", sBuffer.toString(),params.toArray());
	}
	/**
	 * 根据录入的条件查询所有的user
	 * @param phone
	 * @param card
	 * @param title
	 * @return
	 */
	public List<Record> findExcelByPage(String phone, String card, String match) {
		StringBuffer sBuffer = new StringBuffer("SELECT A.*,B.bankName,C.clientCode,D.cardNo as bankCardNo FROM t_bus_user A "
				+ "inner join t_bus_user_change C on A.phone = C.linkPhone "
				+ "left join t_bus_user_bankid B on A.bankNoId = B.bankId "
				+ "left join t_bus_user_card D on D.userid = A.id "
				+ " where 1=1 ");
		List<Object> params = new ArrayList<Object>();
		if(StrKit.notBlank(phone)){
			sBuffer.append(" AND A.phone like ?");
			params.add("%"+phone+"%");
		}
		if(StrKit.notBlank(card)){
			sBuffer.append(" and A.card=?");
			params.add(card);
		}
		if(StrKit.notBlank(match)){
			sBuffer.append(" and C.match=? ");
			params.add(match);
		}
		
		
		sBuffer.append(" ORDER BY A.createDate DESC,A.id desc");
		return Db.find(sBuffer.toString(),params.toArray());
	}
	/**
	 * 导出
	 * @param response
	 * @param request
	 * @param titles
	 * @param listz
	 */
	public void export(HttpServletResponse response, HttpServletRequest request, List<Pair> titles, List<Record> listz) {
		ExcelExportUtil.exportByRecord(response, request, "转换预览导出", titles, listz);
	}
	/**
	 * 导出
	 * @param response
	 * @param request
	 * @param titles
	 * @param listz
	 */
	public void exportChange(HttpServletResponse response, HttpServletRequest request, List<Pair> titles, List<Record> listz) {
		ExcelExportUtil.exportByRecord(response, request, "已同步会员", titles, listz);
	}
	/**
	 * 根据用户手机号查询用户
	 * @param newObject
	 * @return
	 */
	public User findByUserPhone(String phone){
		return User.me().findFirst("select * from t_bus_user where phone = ?", phone);
	}
	/**
	 * 根据用户手机号查询同步信息（转换代码）
	 * @param newObject
	 * @return
	 */
	public UserChange findChangeByPhone (String phone){
		return UserChange.me().findFirst("select clientCode from t_bus_user_change where clientName = ?", phone);
	}
	
	/**
	 * 分页查询配送比例记录
	 * @return
	 */
	public Page<Record> findByPageScale(int pageNum, int pageSize){
		StringBuffer sBuffer = new StringBuffer("FROM t_bus_user_change_scale A where 1=1 ");
		List<Object> params = new ArrayList<Object>();
		sBuffer.append(" ORDER BY A.date DESC");
		return Db.paginate(pageNum, pageSize, "SELECT A.* ", sBuffer.toString(),params.toArray());
	}
	/**
	 * 根据日期查询配送比例记录
	 * @return
	 */
	public Record findScaleByDate(String date) {
		return Db.findFirst("select * FROM t_bus_user_change_scale where date=?",date);
	}
	/**
	 * 添加配送比例
	 * @param re
	 * @return
	 */
	public boolean addScale(Record re) {
		return Db.save("t_bus_user_change_scale", re) ;
	}
	/**
	 * 根据ID配送比例
	 * @param re
	 * @return
	 */
	public Record findScaleByID(int id) {
		return Db.findFirst("select * FROM t_bus_user_change_scale where id=?",id);
	}
	/**
	 * 修改配送比例
	 * @param re
	 * @return
	 */
	public boolean scaleUpdate(Record re) {
		return Db.update("t_bus_user_change_scale", re) ;
	}
	/**
	 * 查询所有的兑换比例
	 * @return
	 */
	public List<Record> findChangeScale() {
		return Db.find("select * from t_bus_user_change_scale order by id desc");
	}
	public UserValueLog findUserValueLog(int userid) {
		// TODO Auto-generated method stub
		return UserValueLog.me().findFirst("select * FROM t_bus_user_value_log where userid=? and flag = 9 order by id desc",userid);
	}

}
